package com.feonix.common.basedao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.feonix.common.annotation.GeneratorType;
import com.feonix.common.util.SpringUtil;
import com.feonix.common.util.StringUtil;
import com.feonix.common.util.jdbc.Dialect;
import com.feonix.common.util.jdbc.SqlLimitFactory;

public class EntitySqlBuilder {
	public static void buildSql(Class<?> clazz) {
		if (isExist(clazz)) {
			return;
		}
		HashMap<SqlType, String> entitySqlMap = new HashMap<SqlType, String>();

		Map<Class<?>, Entity> entityMap = EntityManager.getEntities();
		Entity entity = entityMap.get(clazz);
		if (entity == null) {
			return;
		}
		entitySqlMap.put(SqlType.INSERT, buildInsertSql(entity));
		entitySqlMap.put(SqlType.DELETE, buildDeleteSql(entity));
		entitySqlMap.put(SqlType.SELECT, buildSelectSql(entity));
		entitySqlMap.put(SqlType.MAXID, buildMaxIdSql(entity));
		entitySqlMap.put(SqlType.UPDATE, buildUpdateSql(entity));
		entitySqlMap.put(SqlType.SELECT_FROM_ID, buildSelectFromIdSql(entity));
		entitySqlMap.put(SqlType.DELETE_FROM_IDS, buildDeleteFromIdsSql(entity));
		if (entity.isCreate()) {
			entitySqlMap.put(SqlType.INDEX, buildIndexSql(entity));
			entitySqlMap.put(SqlType.CHECK, buildCheckSql(entity));
			entitySqlMap.put(SqlType.TABLE, buildTableSql(entity));
			if ((entity.getIdProperty() != null)
					&& (entity.getIdProperty().getGeneratorType() == GeneratorType.NATIVE)) {
				entitySqlMap.put(SqlType.SEQ, buildSeq(entity));
				entitySqlMap.put(SqlType.TRIGGER, buildTrigger(entity));
				entitySqlMap.put(SqlType.ENABLE_SEQ, enableSeq(entity));
				entitySqlMap.put(SqlType.DISABLE_SEQ, disableSeq(entity));
				entitySqlMap.put(SqlType.REMOVE_SEQ, removeSeq(entity));
			}
		}
		EntityManager.addEntitySql(clazz, entitySqlMap);
	}

	private static boolean isExist(Class<?> clazz) {
		return (EntityManager.getEntitySql(clazz) != null);
	}

	static String buildInsertSql(Entity entity) {
		StringBuffer sql = new StringBuffer();
		String finalSql = "";
		sql.append("INSERT INTO ").append(entity.getTableName()).append(" (");
		List<EntityProperty> entityPropertyList = entity.getEntityProperties();
		StringBuffer sqlBuffer = new StringBuffer();
		ArrayList<String> columnNameList = new ArrayList<String>();
		for (Iterator<EntityProperty> iterator = entityPropertyList.iterator(); iterator.hasNext();) {
			EntityProperty entityProperty = iterator.next();
			if ((entityProperty.getGeneratorType() != null)
					&& (entityProperty.getGeneratorType() == GeneratorType.NATIVE)) {
				continue;
			}
			columnNameList.add(":" + entityProperty.getColumnName());
		}
		sqlBuffer.append(StringUtil.join(columnNameList, ", "));
		String columnsStr = sqlBuffer.toString();
		sqlBuffer.delete(0, sqlBuffer.length());
		sql.append(columnsStr.replaceAll(":", ""));
		sql.append(") VALUES(").append(columnsStr).append(")");
		finalSql = sql.toString();
		sql.delete(0, sql.length());
		return (finalSql);
	}

	static String buildUpdateSql(Entity entity) {
		if (entity.getIdProperty() == null) {
			return null;
		}
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("UPDATE ").append(entity.getTableName()).append(" SET ");
		List<EntityProperty> entityPropeties = entity.getEntityProperties();
		StringBuffer columns = new StringBuffer();
		ArrayList<String> columnNameList = new ArrayList<String>();
		for (Iterator<EntityProperty> it = entityPropeties.iterator(); it.hasNext();) {
			EntityProperty entityProperty = it.next();
			if (entityProperty.isPk())
				continue;
			if (!(entityProperty.isUpdate())) {
				continue;
			}
			columnNameList.add(entityProperty.getColumnName() + "=:" + entityProperty.getColumnName());
		}
		columns.append(StringUtil.join(columnNameList, ", "));
		String columnsStr = columns.toString();
		columns.delete(0, columns.length());
		sqlStringBuffer.append(columnsStr);
		sqlStringBuffer.append(" WHERE ").append(entity.getIdProperty().getColumnName()).append("=:")
				.append(entity.getIdProperty().getColumnName());
		String finalSql = sqlStringBuffer.toString();
		sqlStringBuffer.delete(0, sqlStringBuffer.length());
		return finalSql;
	}

	static String buildSelectSql(Entity entity) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		List<EntityProperty> entityProperties = entity.getEntityProperties();
		int i = 0;
		for (Iterator<EntityProperty> iterator = entityProperties.iterator(); iterator.hasNext();) {
			EntityProperty entityProperty = iterator.next();
			if (i != 0) {
				sql.append(",");
			}
			sql.append(entityProperty.getColumnName());
			++i;
		}
		sql.append(" FROM ").append(entity.getTableName());
		String finalSql = sql.toString();
		sql.delete(0, sql.length());
		return (finalSql);
	}

	static String buildMaxIdSql(Entity entity) {
		if (entity.getIdProperty() == null) {
			return null;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT MAX(");
		sql.append(entity.getIdProperty().getColumnName());
		sql.append(") FROM ").append(entity.getTableName());
		String str = sql.toString();
		sql.delete(0, sql.length());
		return str;
	}

	static String buildSelectFromIdSql(Entity entity) {
		if (entity.getIdProperty() == null) {
			return null;
		}
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("SELECT ");
		List<EntityProperty> entityProperties = entity.getEntityProperties();
		int i = 0;
		for (Iterator<EntityProperty> iterator = entityProperties.iterator(); iterator.hasNext();) {
			EntityProperty entityProperty = iterator.next();
			if (i != 0) {
				sqlSb.append(",");
			}
			sqlSb.append(entityProperty.getColumnName());
			++i;
		}
		sqlSb.append(" FROM ").append(entity.getTableName()).append(" WHERE ");
		sqlSb.append(entity.getIdProperty().getColumnName()).append(" =:");
		sqlSb.append(entity.getIdProperty().getColumnName());
		String sql = sqlSb.toString();
		sqlSb.delete(0, sqlSb.length());
		return sql;
	}

	static String buildDeleteSql(Entity entity) {
		if (entity.getIdProperty() == null) {
			return null;
		}
		StringBuffer deleteSql = new StringBuffer();
		deleteSql.append("DELETE FROM ").append(entity.getTableName()).append(" WHERE ");
		deleteSql.append(entity.getIdProperty().getColumnName()).append(" =:")
				.append(entity.getIdProperty().getColumnName());
		String str = deleteSql.toString();
		deleteSql.delete(0, deleteSql.length());
		return str;
	}

	static String buildDeleteFromIdsSql(Entity entity) {
		if (entity.getIdProperty() == null) {
			return null;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM ").append(entity.getTableName()).append(" WHERE ");
		sql.append(entity.getIdProperty().getColumnName()).append(" IN(:")
				.append(entity.getIdProperty().getColumnName()).append(")");
		String str = sql.toString();
		sql.delete(0, sql.length());
		return str;
	}

	static String buildTableSql(Entity entity) {
		StringBuffer sql = new StringBuffer();
		SqlLimitFactory sqlLimitFactory = SpringUtil.getBean("sqlLimitFactory", SqlLimitFactory.class);
		Dialect dialect = sqlLimitFactory.getDialect();
		if (dialect == null) {
			return null;
		}
		List<EntityProperty> entityProperties = entity.getEntityProperties();
		sql.append("CREATE TABLE ").append(entity.getTableName()).append("(");
		int i = 0;
		for (Iterator<EntityProperty> entityProperty = entityProperties.iterator(); entityProperty.hasNext();) {
			EntityProperty tmpEntityProperty = entityProperty.next();
			if (i != 0) {
				sql.append(",");
			}
			++i;
			switch (tmpEntityProperty.getColumnType().ordinal() + 1) {
			case 1:
				sql.append(dialect.getDateColumn(tmpEntityProperty));
				break;
			case 2:
				sql.append(dialect.getDateTimeColumn(tmpEntityProperty));
				break;
			case 3:
				sql.append(dialect.getIntColumn(tmpEntityProperty));
				break;
			case 4:
				sql.append(dialect.getDoubleColumn(tmpEntityProperty));
				break;
			case 5:
				sql.append(dialect.getFloatColumn(tmpEntityProperty));
				break;
			case 6:
				sql.append(dialect.getVarcharColumn(tmpEntityProperty));
				break;
			case 7:
				sql.append(dialect.getCharColumn(tmpEntityProperty));
				break;
			
			case 8:
				sql.append(dialect.getTextColumn(tmpEntityProperty));
				break;
			case 9:
				sql.append(dialect.getBlobColumn(tmpEntityProperty));
				break;
			case 10:
				sql.append(dialect.getTextColumn(tmpEntityProperty));
				break;
			}
			if (tmpEntityProperty.getGeneratorType() != null) {
				sql.append(dialect.getGeneratorColumn(tmpEntityProperty));
			}
		}
		sql.append(")");
		sql.append(StringUtil.getString(dialect.getCharset()));
		String sqlstr = sql.toString();
		sql.delete(0, sql.length());
		return sqlstr;
	}

	static String buildSeq(Entity entity) {
		return SqlBuilder.createSeq(entity.getTableName().toUpperCase().trim());
	}

	static String buildTrigger(Entity entity) {
		if (entity.getIdProperty() == null) {
			return null;
		}

		return SqlBuilder.createTrigger(entity.getTableName().toUpperCase(), entity.getTableName().toUpperCase(),
				entity.getTableName().toUpperCase(), entity.getIdProperty().getColumnName());
	}

	static String enableSeq(Entity entity) {
		return SqlBuilder.enableTrigger(entity.getTableName().toUpperCase());
	}

	static String removeSeq(Entity entity) {
		return SqlBuilder.removeTrigger(entity.getTableName().toUpperCase());
	}

	static String disableSeq(Entity entity) {
		return SqlBuilder.disableTrigger(entity.getTableName().toUpperCase());
	}

	static String buildIndexSql(Entity entity) {
		Set<Entry<String, String[]>> indexSet = entity.getIndexes().entrySet();
		String str1 = null;
		String str2 = null;
		ArrayList<String> sqlList = new ArrayList<String>();
		StringBuffer sql = new StringBuffer();
		for (Iterator<Entry<String, String[]>> iterator = indexSet.iterator(); iterator.hasNext();) {
			Entry<String, String[]> obj = iterator.next();
			str2 = obj.getKey().toLowerCase();
			sql.append("CREATE INDEX ");
			sql.append(str2);
			sql.append(" ON ");
			sql.append(entity.getTableName());
			str1 = StringUtil.join(obj.getValue(), ",").toLowerCase();
			sql.append(" (");
			sql.append(str1);
			sql.append(")");
			sqlList.add(sql.toString());
			StringUtil.cleanStringBuffer(new StringBuffer[] { sql });
		}
		String indexSql = StringUtil.join(sqlList, ";");
		return indexSql;
	}

	static String buildCheckSql(Entity entity) {
		StringBuffer checkSql = new StringBuffer();
		checkSql.append("SELECT 1 FROM ").append(entity.getTableName());
		String checkSqlStr = checkSql.toString();
		checkSql.delete(0, checkSql.length());
		return checkSqlStr;
	}
}
